﻿Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Windows.Forms
Imports System.Data.OleDb.OleDbConnection
Imports MySql.Data
Imports MySql.Data.MySqlClient
Imports System.IO
Imports DevExpress.XtraGrid.Views.Grid





Public Class Form5

    Private dataAdapter As New MySqlDataAdapter
    ' Private bindingSource1 As New BindingSource()


    Private Sub Form5_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Me.Size = My.Settings.Form5Size
        FormPos(My.Settings.Form5Pos)
        Me.Location = My.Settings.Form5Pos

        ' Bind the DataGridView to the BindingSource
        ' and load the data from the database.
        '

    End Sub


    Private Sub GridView1_RowCellStyle(sender As Object, e As DevExpress.XtraGrid.Views.Grid.RowCellStyleEventArgs) Handles GridView1.RowCellStyle
        Dim view As GridView = sender
        If e.Column.FieldName = "Status" Then
            Dim myOtherColumnValue As String = view.GetRowCellValue(e.RowHandle, "Status")
            If myOtherColumnValue = "200" Then e.Appearance.BackColor = Color.LightSalmon
            If myOtherColumnValue = "400" Then e.Appearance.BackColor = Color.LightYellow
            If myOtherColumnValue = "500" Then e.Appearance.BackColor = Color.LightGray
            If myOtherColumnValue = "600" Then e.Appearance.BackColor = Color.LightBlue
            If myOtherColumnValue = "610" Then e.Appearance.BackColor = Color.LightPink
            If myOtherColumnValue = "700" Then e.Appearance.BackColor = Color.Violet
            If myOtherColumnValue = "710" Then e.Appearance.BackColor = Color.Red
            If myOtherColumnValue = "900" Then e.Appearance.BackColor = Color.LightGreen

            If myOtherColumnValue = "999" Then e.Appearance.BackColor = Color.Black
            If myOtherColumnValue = "999" Then e.Appearance.ForeColor = Color.Yellow
        End If

    End Sub



    Function GetData(ByVal selectCommand As String)

        Try
            Dim ConCM As MySqlConnection
            ConCM = New MySqlConnection(My.Settings.MySQL)
            ConCM.Open()

            Dim cmd As New MySqlCommand(selectCommand, ConCM)

            Me.dataAdapter = New MySqlDataAdapter(cmd)
            Dim table As New DataTable
            table.Locale = System.Globalization.CultureInfo.InvariantCulture
            Me.dataAdapter.Fill(table)
            Me.BindingSource1.DataSource = table

            ' Resize the DataGridView columns to fit the newly loaded content.
            'Me.DataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)
            GridView1.BestFitColumns()
            If File.Exists(My.Settings.ConfigPath & "\Form5GW01.xml") Then GridView1.RestoreLayoutFromXml(My.Settings.ConfigPath & "\Form5GW01.xml")


            ConCM.Close()

            Me.BarStaticItem1.Caption = "JLD004 / Form5 : " & GridView1.RowCount.ToString


        Catch ex As OleDbException

        End Try
        GetData = ""

    End Function

    Private Sub Form5_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        My.Settings.Form5Size = Me.Size
        My.Settings.Form5Pos = Me.Location

        'Save all gridview setups
        If Not Directory.Exists(My.Settings.ConfigPath) Then Directory.CreateDirectory(My.Settings.ConfigPath)

        Try
            GridView1.SaveLayoutToXml(My.Settings.ConfigPath & "\Form5GW01.xml")
        Catch ex As Exception
        End Try


    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

        Me.Cursor() = Cursors.WaitCursor

        GetData("SELECT from_unixtime(ValDatBodGeplanterabschlus2927/1000) AS Abschluss, ValDatBodExterneauftragsnud106 as Auftragsnummer, " & _
         "ValTueExtauftragsnummereb7b as Internnummer, ValDatBodEinsatznummerccf5 as Einsatznummer, ValAblStatusb22e as Status, ValAblTecMandanted4 as Land, ValAblTecName60ff as Techniker, " & _
         "ValAblTecTechnikeridfc40 as 'Techniker ID', ValDatBodAuftragsartede4 as Auftragstyp , ValDatKunAdrName7979 as Kunde, " & _
         "ValDatObjAdrAdresszeile1787a as Adr1, ValDatObjAdrAdresszeile27879 as Adr2, ValDatObjAdrAdresszeile37878 as Adr3, ValDatObjAdrAdresszeile4787f as Adr4, " & _
         "ValDatObjAdrPlz7bf7 as PLZ, ValDatObjAdrOrt8627 as Ort, ValDatObjAdrEmail1acf as Email, " & _
         "ValDatObjGeoBreitengraddaa3 as Lat, ValDatObjGeoLaengengrad7edc as lng " & _
         "FROM auftragrow JOIN auftragrowvaldatobjtuerene261  ON auftragrow.Kee = auftragrowvaldatobjtuerene261.FkKee  " & _
         "WHERE ValDatBodExterneauftragsnud106 Like '%" & Trim(TBOrder.Text.ToString) & "%' AND ValDatBodMandante3bc = '" & Trim(My.Settings.CadisEnv) & "' " & _
         "ORDER BY ValDatBodExterneauftragsnud106  asc " & _
         "LIMIT 50")

        Me.Cursor() = Cursors.Default

    End Sub

    Private Sub TBOrder_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TBOrder.KeyPress

        If e.KeyChar = Microsoft.VisualBasic.ChrW(Keys.Return) Then

            Me.Cursor() = Cursors.WaitCursor

            GetData("SELECT from_unixtime(ValDatBodGeplanterabschlus2927/1000) AS Abschluss, ValDatBodExterneauftragsnud106 as Auftragsnummer, " & _
             "ValTueExtauftragsnummereb7b as Internnummer, ValDatBodEinsatznummerccf5 as Einsatznummer, ValAblStatusb22e as Status, ValAblTecMandanted4 as Land, ValAblTecName60ff as Techniker, " & _
             "ValAblTecTechnikeridfc40 as 'Techniker ID', ValDatBodAuftragsartede4 as Auftragstyp , ValDatKunAdrName7979 as Kunde, " & _
             "ValDatObjAdrAdresszeile1787a as Adr1, ValDatObjAdrAdresszeile27879 as Adr2, ValDatObjAdrAdresszeile37878 as Adr3, ValDatObjAdrAdresszeile4787f as Adr4, " & _
             "ValDatObjAdrPlz7bf7 as PLZ, ValDatObjAdrOrt8627 as Ort, ValDatObjAdrEmail1acf as Email, " & _
             "ValDatObjGeoBreitengraddaa3 as Lat, ValDatObjGeoLaengengrad7edc as lng " & _
             "FROM auftragrow JOIN auftragrowvaldatobjtuerene261  ON auftragrow.Kee = auftragrowvaldatobjtuerene261.FkKee  " & _
             "WHERE ValDatBodExterneauftragsnud106 Like '%" & Trim(TBOrder.Text.ToString) & "%' AND ValDatBodMandante3bc = '" & Trim(My.Settings.CadisEnv) & "' " & _
             "ORDER BY ValDatBodExterneauftragsnud106  asc " & _
             "LIMIT 500")

            Me.Cursor() = Cursors.Default

        End If
    End Sub



    Private Sub BarButtonItem1_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem1.ItemClick

        Me.Cursor() = Cursors.WaitCursor

        GetData("SELECT from_unixtime(ValDatBodGeplanterabschlus2927/1000) AS Abschluss, ValDatBodExterneauftragsnud106 as Auftragsnummer, " & _
         "ValTueExtauftragsnummereb7b as Internnummer, ValDatBodEinsatznummerccf5 as Einsatznummer, ValAblStatusb22e as Status, ValAblTecMandanted4 as Land, ValAblTecName60ff as Techniker, " & _
         "ValAblTecTechnikeridfc40 as 'Techniker ID', ValDatBodAuftragsartede4 as Auftragstyp , ValDatKunAdrName7979 as Kunde, " & _
         "ValDatObjAdrAdresszeile1787a as Adr1, ValDatObjAdrAdresszeile27879 as Adr2, ValDatObjAdrAdresszeile37878 as Adr3, ValDatObjAdrAdresszeile4787f as Adr4, " & _
         "ValDatObjAdrPlz7bf7 as PLZ, ValDatObjAdrOrt8627 as Ort, ValDatObjAdrEmail1acf as Email, " & _
         "ValDatObjGeoBreitengraddaa3 as Lat, ValDatObjGeoLaengengrad7edc as lng " & _
         "FROM auftragrow JOIN auftragrowvaldatobjtuerene261  ON auftragrow.Kee = auftragrowvaldatobjtuerene261.FkKee  " & _
         "WHERE ValDatBodExterneauftragsnud106 Like '%" & Trim(TBOrder.Text.ToString) & "%' AND ValDatBodMandante3bc = '" & Trim(My.Settings.CadisEnv) & "' " & _
         "ORDER BY ValDatBodExterneauftragsnud106  asc " & _
         "LIMIT 500")

        Me.Cursor() = Cursors.Default
    End Sub

    Private Sub BarButtonItem2_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem2.ItemClick

        Me.Cursor() = Cursors.WaitCursor

        GetData("SELECT from_unixtime(ValDatBodGeplanterabschlus2927/1000) AS Abschluss, ValDatBodExterneauftragsnud106 as Auftragsnummer, " & _
         "ValTueExtauftragsnummereb7b as Internnummer, ValDatBodEinsatznummerccf5 as Einsatznummer, ValAblStatusb22e as Status, ValAblTecMandanted4 as Land, ValAblTecName60ff as Techniker, " & _
         "ValAblTecTechnikeridfc40 as 'Techniker ID', ValDatBodAuftragsartede4 as Auftragstyp , ValDatKunAdrName7979 as Kunde, " & _
         "ValDatObjAdrAdresszeile1787a as Adr1, ValDatObjAdrAdresszeile27879 as Adr2, ValDatObjAdrAdresszeile37878 as Adr3, ValDatObjAdrAdresszeile4787f as Adr4, " & _
         "ValDatObjAdrPlz7bf7 as PLZ, ValDatObjAdrOrt8627 as Ort, ValDatObjAdrEmail1acf as Email, " & _
         "ValDatObjGeoBreitengraddaa3 as Lat, ValDatObjGeoLaengengrad7edc as lng " & _
         "FROM auftragrow JOIN auftragrowvaldatobjtuerene261  ON auftragrow.Kee = auftragrowvaldatobjtuerene261.FkKee  " & _
         "WHERE ValAblStatusb22e < '710' AND ValAblTecMandanted4 ='" & Trim(My.Settings.CadisEnv) & "' AND ValDatBodGeplanterabschlus2927 >= '1262453880000'")

        Me.Cursor() = Cursors.Default

    End Sub

    Private Sub BarButtonItem3_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem3.ItemClick
        Me.Cursor() = Cursors.WaitCursor
        Dim s As String
        Dim sfrom As String
        Dim sto As String

        Try
            s = Trim(TBTechniker.EditValue.ToString)
        Catch ex As Exception
            s = ""
        End Try

        Try
            sto = Trim(TBTO.EditValue.ToString)
        Catch ex As Exception
            sto = ""
        End Try

        Try
            sfrom = Trim(TBFrom.EditValue.ToString)
        Catch ex As Exception
            sfrom = ""
        End Try


        GetData("SELECT from_unixtime(ValDatBodGeplanterabschlus2927/1000) AS Abschluss, ValDatBodExterneauftragsnud106 as Auftragsnummer, " & _
         "ValTueExtauftragsnummereb7b as Internnummer, ValDatBodEinsatznummerccf5 as Einsatznummer, ValAblStatusb22e as Status, ValAblTecMandanted4 as Land, ValAblTecName60ff as Techniker, " & _
         "ValAblTecTechnikeridfc40 as 'Techniker ID', ValDatBodAuftragsartede4 as Auftragstyp , ValDatKunAdrName7979 as Kunde, " & _
         "ValDatObjAdrAdresszeile1787a as Adr1, ValDatObjAdrAdresszeile27879 as Adr2, ValDatObjAdrAdresszeile37878 as Adr3, ValDatObjAdrAdresszeile4787f as Adr4, " & _
         "ValDatObjAdrPlz7bf7 as PLZ, ValDatObjAdrOrt8627 as Ort, ValDatObjAdrEmail1acf as Email, " & _
         "ValDatObjGeoBreitengraddaa3 as Lat, ValDatObjGeoLaengengrad7edc as lng " & _
         "FROM auftragrow JOIN auftragrowvaldatobjtuerene261  ON auftragrow.Kee = auftragrowvaldatobjtuerene261.FkKee  " & _
         "WHERE ValAblStatusb22e <= '" & sto & "' " & _
         "AND ValAblStatusb22e >= '" & sfrom & "'AND ValAblTecMandanted4 = '" & Trim(My.Settings.CadisEnv) & "' AND ValDatBodGeplanterabschlus2927 >= '1262453880000' " & _
         "AND ValAblTecTechnikeridfc40 Like '%" & s & "%'")

        Me.Cursor() = Cursors.Default
    End Sub

    Private Sub BarButtonItem4_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem4.ItemClick
        'Load data to excel
        Const filename As String = "c:\JLD004_Form5_Data.xls"

        If File.Exists(filename) Then
            File.Delete(filename)
        End If

        GridControl1.ExportToXls(filename)

        Dim app = New Microsoft.Office.Interop.Excel.Application
        app.Visible = True

        app.Workbooks.Open(System.IO.Path.GetFullPath(filename))
    End Sub

    Private Sub OpenPDFToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles OpenPDFToolStripMenuItem.Click

        Dim s As String
        Dim s1 As String = ""
        Dim Mask As String = ""
        Dim sPDFfile As String = ""
        Dim JLAUF As String = Trim(GridView1.GetRowCellValue(GridView1.FocusedRowHandle, "Auftragsnummer"))

        s = Form1.GetPDFData("SELECT JLPDF FROM F5001 WHERE JLEXTNUM = '" & JLAUF & "' OR JLAUF = '" & JLAUF & "'")

        If s <> "" Then
            'Open PDF new methode
            Mask = My.Settings.PDFPath & "\" & s
            sPDFfile = Dir(Mask, vbNormal)
            If sPDFfile <> "" Then Process.Start(My.Settings.PDFPath & "\" & sPDFfile)

        Else
            'Open PDF old methode
            Try
                s = GetData2("select top 1 m3ordernumber from dbo.M3Order  where idm in (select ID from auf where auftragsnummer = '" & JLAUF & "')")

                s = s.Substring(2, s.Length - 2)

                Mask = My.Settings.PDFPath & "\*" & s & "*.PDF"
                sPDFfile = Dir(Mask, vbNormal)
                If sPDFfile <> "" Then Process.Start(My.Settings.PDFPath & "\" & sPDFfile)

            Catch ex As Exception

            End Try
        End If

    End Sub


    Function GetData2(ByVal selectCommand As String)
        GetData2 = ""

        Try
            Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
            Dim cmd As New System.Data.SqlClient.SqlCommand
            Dim reader As SqlClient.SqlDataReader = Nothing

            cmd.CommandText = selectCommand
            cmd.Connection = sqlConnection1

            sqlConnection1.Open()

            reader = cmd.ExecuteReader
            While reader.Read
                GetData2 = Trim(reader(0).ToString)
            End While
            sqlConnection1.Close()

        Catch ex As OleDbException

        End Try


    End Function

    Private Sub ContextMenuStrip1_Opening(sender As Object, e As System.ComponentModel.CancelEventArgs) Handles ContextMenuStrip1.Opening
        Dim i As String = GridView1.GetRowCellValue(GridView1.FocusedRowHandle, "Status")

        'schow PDF menu
        If (i = "710" Or i = "900" Or i = "999") Then
            ContextMenuStrip1.Items(0).Visible = True
        Else
            ContextMenuStrip1.Items(0).Visible = False
        End If
    End Sub
End Class